import pandas as pd
import os
import PyPDF2
import re


def pdf_to_excel(pdf_path, excel_path=None):
    # 设置输出路径
    if not excel_path:
        base = os.path.splitext(pdf_path)[0]
        excel_path = f"{base}.xlsx"

    # 读取 PDF 文件内容
    with open(pdf_path, 'rb') as file:
        reader = PyPDF2.PdfReader(file)
        content = ""
        for page in reader.pages:
            content += page.extract_text() + "\n"

    # 预处理文本内容，提取表格数据
    lines = content.split('\n')
    data = []
    current_record = None
    date_pattern = re.compile(r'^\d{4}-\d{2}-\d{2}')  # 匹配日期格式

    # 定义识别模式
    transaction_types = [
        "转账汇款", "汇入汇款", "行内转账转入", "银联快捷支付", "账户结息",
        "银联代付", "网联收款", "转账退款", "快捷支付", "快捷退款", "账户信息"
    ]
    footer_keywords = ["温馨提示", "交易流水验真", "www.cmbchina.com", "验真提示", "在线服务"]
    header_keywords = ["记账日期", "Date", "货币", "Currency", "交易金额", "Transaction Amount",
                       "联机余额", "Balance", "交易摘要", "Transaction Type", "对手信息", "Counter Party"]
    separator_pattern = re.compile(r'[-=~_]{5,}')  # 匹配分隔线
    page_number_pattern = re.compile(r'\d{1,2}/\d{1,2}$')  # 匹配页码

    for line in lines:
        # 清理行内容
        line = line.strip()
        if not line:
            continue

        # 检查是否是页脚
        if any(keyword in line for keyword in footer_keywords):
            break  # 遇到页脚直接停止处理

        # 检查是否是分隔线
        if separator_pattern.search(line):
            continue

        # 检查是否是表头行
        if any(keyword in line for keyword in header_keywords):
            # 如果当前有记录，先保存
            if current_record is not None:
                data.append(current_record)
                current_record = None
            continue  # 跳过表头行

        # 检查是否是日期行（新记录开始）
        if date_pattern.match(line):
            # 保存上一条记录
            if current_record is not None:
                data.append(current_record)

            # 处理新记录
            parts = line.split(maxsplit=4)  # 只分割前4个字段，保留剩余部分完整

            # 确保有足够的部分构成基本记录
            if len(parts) >= 5:
                # 提取基础字段
                date = parts[0]
                currency = parts[1]
                transaction_amount = parts[2]
                balance = parts[3]
                remaining = parts[4]  # 剩余部分（交易类型+对手信息）

                # 识别交易类型
                transaction_type = ""
                counter_party = ""

                # 尝试从剩余部分识别交易类型
                for tt in transaction_types:
                    if tt in remaining:
                        transaction_type = tt
                        # 提取对手信息（交易类型之后的内容）
                        counter_party = remaining.replace(tt, "", 1).strip()
                        break

                # 如果没有识别到交易类型，尝试分割第一个词
                if not transaction_type:
                    # 尝试按空格分割第一个词
                    first_space = remaining.find(" ")
                    if first_space > 0:
                        possible_tt = remaining[:first_space]
                        # 检查是否可能是交易类型
                        if any(tt in possible_tt for tt in transaction_types):
                            transaction_type = possible_tt
                            counter_party = remaining[first_space:].strip()

                # 如果仍然没有识别到，使用整个剩余部分作为交易类型
                if not transaction_type:
                    transaction_type = remaining

                # 构建记录
                current_record = {
                    "Date": date,
                    "Currency": currency,
                    "Transaction Amount": transaction_amount,
                    "Balance": balance,
                    "Transaction Type": transaction_type,
                    "Counter Party": counter_party
                }
            continue

        # 处理跨行内容（添加到对手信息）
        if current_record is not None:
            # 移除页码
            clean_line = page_number_pattern.sub('', line).strip()

            # 如果清理后还有内容，追加到对手信息
            if clean_line:
                # 检查是否是新的交易记录意外开始
                if not date_pattern.match(clean_line):
                    # 检查是否是下一页的表头
                    is_next_page_header = any(keyword in clean_line for keyword in header_keywords)

                    if not is_next_page_header:
                        current_record["Counter Party"] += " " + clean_line
            continue

    # 添加最后一条记录
    if current_record is not None:
        data.append(current_record)

    # 创建 DataFrame
    df = pd.DataFrame(data)

    # 后处理：清理对手信息中的无关内容
    def clean_counter_party(text):
        if not text:
            return ""
        # 移除页脚相关内容
        text = re.sub(r'温馨提示.*$', '', text)
        text = re.sub(r'交易流水验真.*$', '', text)
        text = re.sub(r'进入一网通首页.*$', '', text)
        text = re.sub(r'点击.*查询.*$', '', text)
        # 移除表头相关内容
        for keyword in header_keywords:
            text = text.replace(keyword, "")
        # 移除多余空格
        return re.sub(r'\s+', ' ', text).strip()

    if "Counter Party" in df.columns:
        df["Counter Party"] = df["Counter Party"].apply(clean_counter_party)

    # 清理交易类型中的额外内容
    def clean_transaction_type(text):
        # 只保留核心交易类型
        for tt in transaction_types:
            if tt in text:
                return tt
        return text

    if "Transaction Type" in df.columns:
        df["Transaction Type"] = df["Transaction Type"].apply(clean_transaction_type)

    # 写入 Excel
    df.to_excel(excel_path, index=False)
    print(f"\n转换完成! 结果已保存至: {excel_path}")
    return True


if __name__ == "__main__":
    # 指定 PDF 文件路径
    pdf_path = r"C:\Users\Administrator\Desktop\招商银行交易流水(申请时间2025年07月01日19时07分49秒).pdf"

    # 检查文件是否存在
    if not os.path.exists(pdf_path):
        print(f"文件 {pdf_path} 不存在")
        exit(1)

    # 使用文件名作为输出路径
    excel_path = os.path.splitext(pdf_path)[0] + ".xlsx"

    if pdf_to_excel(pdf_path=pdf_path, excel_path=excel_path):
        print("操作成功完成！")
    else:
        print("操作失败，请检查错误信息。")